﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using NHibernateVertica;
using Vertica.Data.VerticaClient;
using VerticaAPI.model;
using static VerticaAPI.model.EndsDisplayMeasureModel;
using System.Data;

namespace VerticaAPI
{
    /// <summary>
    /// 端头陈列量
    /// </summary>
    [Description("端头陈列量设置")]
    public class EndsDisplayMeasureHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region 查询

        #region 端头陈列量列表

        [MustLogin]
        [Description("端头陈列量列表")]
        [ReturnType(typeof(IList<EndsDisplayMeasureModel.EndsDisplay>))]
        public ApiResult GetEndsDisplayMeasurelist(
            [ParamDescription("店铺名称或者店铺编码")] string keys = "",
            [ParamDescription("店铺编码列表")] string ShopList = "",
            [ParamDescription("货号")] string materialId = "",
            [ParamDescription("页码")] int Size = 1,
            [ParamDescription("页数")] int Page = 100)

        {

            try
            {
                //ShopID没有数据，改成ShopCode
                string ShopCodeList = ReturnShopList(ShopList);
                if (!string.IsNullOrEmpty(ShopCodeList))
                {
                    string ShopCodeSql = $"SELECT ShopCode FROM ndw.ndw_Shop where shopCode IN ({ShopCodeList})";
                    var ShopCodecmd = Helper.GetSqlStringCommand(ShopCodeSql);
                    var ShopCodedt = Helper.ExecuteDataTable(ShopCodecmd);
                    var IsExlist = ModelConvertHelper<EndsDisplayImportModel>.ConvertToModel(ShopCodedt);

                    string returnStr = "'";
                    foreach (var item in IsExlist)
                    {
                        ShopCodeList += item.ShopCode + "','";
                    }
                    returnStr = returnStr.TrimEnd('\'');
                    ShopCodeList = returnStr.TrimEnd(',');
                }


                var count = ReturnCount(keys, materialId, ShopList);
                var result = PaperData<IGrouping<string, EndsDisplayMeasureModel.EndsDisplay>>.Create(count, Size, Page);
                string sql =
                    $@"  SELECT a.ExtrSource,a.CompanyID,a.ShopID,a.ShopCode,shop.ShopName as ShopName,a.MaterialID,a.DisplayQty FROM ndw.ndw_ShelfEndDisPlay a
                                  LEFT JOIN ndw.ndw_Shop shop on a.shopcode = shop.shopcode and a.ExtrSource=shop.ExtrSource and a.CompanyID=shop.CompanyID
                                    WHERE a.ExtrSource = '{UserInfo.UserExtrSource}' AND a.CompanyID = '{UserInfo.UserCompanyId}'
                                    {(string.IsNullOrWhiteSpace(keys) ? "" : $" AND (a.shopcode like '%{keys}%' or shop.shopname like '%{keys}%') ")}
                                    {(string.IsNullOrWhiteSpace(ShopCodeList) ? "" : $" AND a.ShopCode in({ShopCodeList})")} 
                                    {(string.IsNullOrWhiteSpace(materialId) ? "" : $" AND (a.MaterialID ='{materialId}') ")}
                                      LIMIT {Size} offset {(Page - 1.0) * Size}	";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<EndsDisplayMeasureModel.EndsDisplay>.ConvertToModel(dt);
                var groupedList = list.GroupBy(p => p.CompanyID + p.ShopID + p.MaterialID);
                result.Items = groupedList.ToList();

                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region  处理门店ID

        public string ReturnShopList(string str)
        {
            if (string.IsNullOrEmpty(str))
                return "";
            string returnStr = "'";
            string[] arr = null;
            arr = str.Split(',');

            for (int i = 0; i < arr.Length; i++)
            {
                returnStr += arr[i] + "','";
            }
            returnStr = returnStr.TrimEnd('\'');
            returnStr = returnStr.TrimEnd(',');

            return returnStr;
        }

        #endregion

        #region 返回行数

        public int ReturnCount(
            [ParamDescription("店铺名称或者店铺编码")] string keys = "",
            [ParamDescription("货号")] string materialId = "", [ParamDescription("店铺编码列表")] string ShopList = "")
        {
            try
            {
                var countSql = string.Empty;

                #region 查询时获取行数

                countSql = $@" SELECT count(1) FROM ndw.ndw_ShelfEndDisPlay a
                               LEFT JOIN ndw.ndw_Shop shop on a.shopcode = shop.shopcode and a.ExtrSource=shop.ExtrSource and a.CompanyID=shop.CompanyID
                               where a.ExtrSource='{UserInfo.UserExtrSource}' and a.CompanyID='{
                        UserInfo.UserCompanyId
                    }'
                                {
                        (string.IsNullOrWhiteSpace(keys)
                            ? ""
                            : $" and (a.shopcode ilike '%{keys}%' or shop.shopname like '%{keys}%') ")
                    }
                                {
                        (string.IsNullOrWhiteSpace(materialId) ? "" : $" and a.materialId ilike '%{materialId}%' ")
                    }
                                {
                        (string.IsNullOrWhiteSpace(ShopList) ? "" : $" and a.ShopID in({ReturnShopList(ShopList)})")
                    }";

                #endregion 查询时获取行数

                var cmd = Helper.GetSqlStringCommand(countSql);
                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                return count;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion 返回行数

        #endregion


        #region 删除

        [JsonType]
        [MustLogin]
        [Description("删除数据")]
        [ReturnType(typeof(IList<EndsDisplayMeasureModel.EndsDisplay>))]
        public ApiResult Remove([ParamDescription("删除实体List")] EndsDisplayDeleteModelList param)
        {

            if (param == null || param.List.Count < 1)
            {
                return Error($"删除失败!");
            }
            HashSet<string> DeleteSql = new HashSet<string>();
            foreach (var item in param.List)
            {
                string deleteSql = $@"Delete FROM ndw.ndw_ShelfEndDisPlay
                                    where ShopCode='{item.ShopCode}' and 
                                    MaterialID='{item.MaterialID}' and
	                                ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}';";
                DeleteSql.Add(deleteSql);
            }
            StringBuilder strSql = new StringBuilder();
            foreach (var item in DeleteSql)
            {
                strSql.Append(item);
            }
            if (strSql.Length > 0)
            {
                List<CommandInfo> cmdList = new List<CommandInfo>();
                CommandInfo cmd = new CommandInfo { CommandText = strSql.ToString() };
                cmdList.Add(cmd);
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            return Error($"删除失败!");
        }

        #endregion 删除


        #region Insert 新增

        [MustLogin]
        [JsonType]
        [Description("新增")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult Insert([ParamDescription("要插入的数据")] EndsDisplaySaveList param)
        {
            #region 前端参数验证 

            if (string.IsNullOrWhiteSpace(param.MaterialID))
            {
                return Error($"新增失败！货号不能为空");
            }
            param.MaterialID = param.MaterialID.Trim();
            if (string.IsNullOrWhiteSpace(param.ShopCode))
            {
                return Error($"新增失败！店铺编号不能为空");
            }
            if (string.IsNullOrWhiteSpace(param.DisplayQty.ToString()))
            {
                return Error($"新增失败！最低陈列量不能为空");
            }
            if (param.DisplayQty <= 0 || param.DisplayQty > 999)
            {
                return Error($"新增失败！最低陈列量范围在1 - 999");
            }

            #endregion

            #region 检测该是否已存在，避免插入重复数据


            if (AlreadyExists(param.ShopCode, param.MaterialID))
            {
                return Error($"新增失败！\r\n【{param.ShopCode}】,货号【{param.MaterialID}】已存在");
            }
            if (!MaterialIDExists(param.MaterialID))
            {
                return Error($"新增失败！\r\n 货号【{param.MaterialID}】 不存在");
            }
            #endregion

            //计算
            // Compute(param);

            #region 插入新数据

            var list = new List<CommandInfo>();

            string insertSql =
                $@" INSERT INTO ndw.ndw_ShelfEndDisPlay (ExtrSource,CompanyID,ShopID,ShopCode,MaterialID,DisplayQty)
	                VALUES(
		                '{UserInfo.UserExtrSource}',
		                '{UserInfo.UserCompanyId}',
		                '{param.ShopID}',
		                '{param.ShopCode}',
		                '{param.MaterialID}',
		                '{param.DisplayQty}' 
                        )";
            list.Add(new CommandInfo(insertSql, null));

            #endregion

            int n = Helper.ExecuteSqlTran(list);
            if (n > 0)
            {
                return Success(0);
            }
            return Error("插入失败,请重试");
        }


        #endregion


        #region Update 修改

        [MustLogin]
        [JsonType]
        [Description("修改")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult Update([ParamDescription("要插入的数据")] EndsDisplaySaveList param)
        {
            #region 前端参数验证 

            if (string.IsNullOrWhiteSpace(param.MaterialID))
            {
                return Error($"修改失败！货号不能为空");
            }
            param.MaterialID = param.MaterialID.Trim();
            if (!MaterialIDExists(param.MaterialID))
            {
                return Error($"修改失败！\r\n 货号【{param.MaterialID}】 不存在");
            }
            if (string.IsNullOrWhiteSpace(param.ShopCode))
            {
                return Error($"修改失败！店铺编号不能为空");
            }
            if (string.IsNullOrWhiteSpace(param.DisplayQty.ToString()))
            {
                return Error($"修改失败！最低陈列量不能为空");
            }
            if (param.DisplayQty <= 0 || param.DisplayQty > 999)
            {
                return Error($"修改失败！最低陈列量范围在1 - 999");
            }
            //如果旧门店 、货号都不同才做效验
            if (param.ShopCode!= param.OldShopCode|| param.MaterialID!=param.OldMaterialID)
            {
                if (AlreadyExists(param.ShopCode, param.MaterialID))
                {
                    return Error($"修改失败！\r\n数据 【{param.ShopCode}】,货号【{param.MaterialID}】已存在！");
                }

            }
          
            #endregion

            var list = new List<CommandInfo>();

            string Sql = $@"UPDATE ndw.ndw_ShelfEndDisPlay SET ShopID='{param.ShopID}',ShopCode='{param.ShopCode}',MaterialID ='{param.MaterialID}',DisplayQty={param.DisplayQty} 
                    WHERE  ShopCode='{param.OldShopCode}' AND MaterialID ='{param.OldMaterialID}'
                    AND ExtrSource='{UserInfo.UserExtrSource}' AND CompanyID='{UserInfo.UserCompanyId}'";
            list.Add(new CommandInfo(Sql, null));
            int n = Helper.ExecuteSqlTran(list);
            if (n > 0)
            {
                return Success(0);
            }
            return Error("更新失败,请重试");
        }



        public bool AlreadyExists(string ShopCode, string materialID)
        {
            bool result = false;
            try
            {
                string strSql = $@" SELECT COUNT(CompanyID) as COUNT FROM ndw.ndw_ShelfEndDisPlay 
                            WHERE ShopCode ='{ShopCode}' and MaterialID='{materialID}' and  ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' ";
                var cmd = Helper.GetSqlStringCommand(strSql);
                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                if (count > 0)
                {
                    result = true;
                }
            }
            catch (Exception e)
            {
                throw;
            }
            return result;
        }


        #endregion

        #region 验证货号是否存在
        public bool MaterialIDExists(string materialID)
        {
            bool result = false;
            try
            {
                string strSql = $@" select count(MaterialID) From nods.nods_erp_sd_mat_material where  ExtrSource='{UserInfo.UserExtrSource}' and MaterialID='{materialID}'";
                var cmd = Helper.GetSqlStringCommand(strSql);
                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                if (count > 0)
                {
                    result = true;
                }
            }
            catch (Exception e)
            {
                throw;
            }
            return result;
        }


        #endregion

        #region 导入
        /// <summary>
        /// 导入数据，修改和新增
        /// </summary>
        /// <param name="list"></param>
        /// <param name="index"></param>
        [Description("导入数据")]
        //public void ChangeList(List<EndsDisplayImportModel>  list, out int index)
        public void ChangeList(IList<EndsDisplayImportModel> list, out DataTable error_dt, out int index)
        {
            var dellist = new List<CommandInfo>();
            var inslist = new List<CommandInfo>();
            List<EndsDisplayImportModel> newlist = new List<EndsDisplayImportModel>();//排除门店不存在后的list
            List<EndsDisplayImportModel> newlists = new List<EndsDisplayImportModel>();//排除门店不存在且货号不存在的
            string ShopCode = string.Empty;
            string MaterialID = string.Empty;

           // string msg = string.Empty;
            List<EndsDisplayImportModel> msgList = new List<EndsDisplayImportModel>();
            foreach (var item in list)
            {
                ShopCode += item.ShopCode + ",";
                MaterialID += item.MaterialID + ",";
            }
            ShopCode = ReturnShopList(ShopCode.Trim(','));
            MaterialID = ReturnShopList(MaterialID.Trim(','));

            #region 查询出门店编码是否存在，不存在的直接排除不进行保存
            if (!string.IsNullOrEmpty(ShopCode))
            {
                string IsExSql = $@"SELECT ShopCode,ShopID FROM ndw.ndw_Shop where shopCode IN ({ShopCode}) 
                                    AND  ExtrSource='{UserInfo.UserExtrSource}' AND CompanyID='{UserInfo.UserCompanyId}'";

                var cmd = Helper.GetSqlStringCommand(IsExSql);
                var dt = Helper.ExecuteDataTable(cmd);
                var IsExlist = ModelConvertHelper<EndsDisplayImportModel>.ConvertToModel(dt);
                foreach (var item in list)
                {
                    foreach (var IsExitem in IsExlist)
                    {
                        if (item.ShopCode == IsExitem.ShopCode)
                        {
                            item.ShopID = IsExitem.ShopID;
                            goto SkipMsg;//如果有存在的就跳出外层list的循环 
                        }

                    }
                    //msgstr = new EndsDisplayImportModel();
                    //msgstr.ShopCode = $"门店编码【{ item.ShopCode}】不存在。";
                    msgList.Add(new EndsDisplayImportModel { ShopCode = $"门店编码【{ item.ShopCode}】不存在。", ShopName = item.ShopName, ShopID = item.ShopID, DisplayQty = item.DisplayQty, MaterialID = item.MaterialID } );
                   
                   // msg += $"<br/>门店编码【{item.ShopCode}】不存在。";
                    continue;//不执行下面添加newlist的数据

                    //将正常的门店信息放到新的集合保存,并且进入下一个循环
                    SkipMsg:
                    {
                        newlist.Add(item);
                        continue;
                    }
                }
              
            }
            #endregion

            #region 查询出货号是否存在，不存在的直接排除不进行保存
            if (!string.IsNullOrEmpty(MaterialID))
            {
                string IsExSql = $@"select MaterialID From nods.nods_erp_sd_mat_material where MaterialID IN ({MaterialID}) 
                                    AND  ExtrSource='{UserInfo.UserExtrSource}'";

                var cmd = Helper.GetSqlStringCommand(IsExSql);
                var dt = Helper.ExecuteDataTable(cmd);
                var IsExlist = ModelConvertHelper<EndsDisplayImportModel>.ConvertToModel(dt);
                foreach (var item in newlist)
                {
                    foreach (var IsExitem in IsExlist)
                    {
                        if (item.MaterialID == IsExitem.MaterialID)
                        {
                            // item.MaterialID = IsExitem.MaterialID;
                            goto SkipMsg;//如果有存在的就跳出外层list的循环 
                        }
                    } 
                   // msgstr.MaterialID = $"货号【{item.MaterialID}】不存在。";
                    msgList.Add(new EndsDisplayImportModel { ShopCode = item.ShopCode, ShopName = item.ShopName, ShopID = item.ShopID, DisplayQty = item.DisplayQty, MaterialID = $"货号【{item.MaterialID}】不存在。" });
                    //msg += $"货号【{item.MaterialID}】不存在。";

                    continue;//不执行下面添加newlists的数据

                    //将正常的货号放到新的集合保存,并且进入下一个循环
                    SkipMsg:
                    {
                        newlists.Add(item);
                        continue;
                    }
                }
              
            }
            #endregion


            foreach (var item in newlists)
            {

                //先删除数据 //shopID='{item.ShopID}' AND
                string sql = $@"DELETE FROM ndw.ndw_ShelfEndDisPlay  WHERE  ShopCode='{item.ShopCode}' AND MaterialID='{item.MaterialID}' AND 
	                                ExtrSource='{UserInfo.UserExtrSource}' AND CompanyID='{UserInfo.UserCompanyId}';";
                dellist.Add(new CommandInfo(sql, null));
                //再进行插入
                sql = $@" INSERT INTO ndw.ndw_ShelfEndDisPlay (ExtrSource,CompanyID,ShopID,ShopCode,MaterialID,DisplayQty)
	                VALUES( '{UserInfo.UserExtrSource}', '{UserInfo.UserCompanyId}','{item.ShopID}','{item.ShopCode}','{item.MaterialID}','{item.DisplayQty}')";
                inslist.Add(new CommandInfo(sql, null));
            }
            //先删除
            Helper.ExecuteSqlTran(dellist);
            //插入
            Helper.ExecuteSqlTran(inslist);
            index = newlists.Count;
            error_dt = DataTableHelper.LinqToDataTable(msgList);
        }







        #endregion



    }
}
